Skip Headers

Oracle® Database JDBC Developer's Guide and Reference 10g Release 2 (10.2) Part Number B14355-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# D Troubleshooting

This appendix describes how to troubleshoot a Java Database Connectivity (JDBC) application or applet, and contains the following topics:

- Common Problems

- Basic Debugging Procedures

## Common Problems

This section describes some common problems that you might encounter while using the Oracle JDBC drivers. These problems include:

- Memory Consumption for CHAR Columns Defined as OUT or IN/OUT Variables

- Memory Leaks and Running Out of Cursors

- Boolean Parameters in PL/SQL Stored Procedures

- Opening More Than 16 OCI Connections for a Process

- Using statement.cancel

- Using JDBC with Firewalls

### Memory Consumption for CHAR Columns Defined as OUT or IN/OUT Variables

In PL/SQL, when a `CHAR` or a `VARCHAR` column is defined as a `OUT` or `IN/OUT` variable, the driver allocates a `CHAR` array of 32512 chars. This can cause a memory consumption problem. Note that `VARCHAR2` columns do not exhibit this behavior.

At previous releases, the solution to the problem was to invoke the `Statement.setMaxFieldSize` method. A better solution is to use `OracleCallableStatement.registerOutParameter`.

We encourage you always to call `registerOutParameter(int paramIndex, int sqlType, int scale, int maxLength)` on each `CHAR` or `VARCHAR` column. This method is defined in `oracle.jdbc.driver.OracleCallableStatement`. Use the fourth argument, `maxLength`, to limit the memory consumption. `maxLength` tells the driver how many characters are necessary to store this column. The column will be truncated if the character array cannot hold the column data. The third argument, `scale`, is ignored by the driver.

### Memory Leaks and Running Out of Cursors

If you receive messages that you are running out of cursors or that you are running out of memory, make sure that all your `Statement` and `ResultSet` objects are explicitly closed. The Oracle JDBC drivers do not have finalizer methods. They perform cleanup routines by using the `close` method of the `ResultSet` and `Statement` classes. If you do not explicitly close your result set and statement objects, significant memory leaks can occur. You could also run out of cursors in the database. Closing a statement releases the corresponding cursor in the database.

Similarly, you must explicitly close `Connection` objects to avoid leaking and running out of cursors on the server side. When you close the connection, the JDBC driver closes any open statement objects associated with it, thus releasing the cursor on the server side.

### Boolean Parameters in PL/SQL Stored Procedures

The JDBC drivers do not support the passing of `BOOLEAN` parameters to PL/SQL stored procedures. If a PL/SQL procedure contains `BOOLEAN` values, you can work around the restriction by wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts the argument as an `INT` and passes it to the first stored procedure. When the second procedure is called, the server performs the conversion from `INT` to `BOOLEAN`.

The following is an example of a stored procedure, `BOOLPROC`, that attempts to pass a `BOOLEAN` parameter, and a second procedure, `BOOLWRAP`, that performs the substitution of an `INT` value for the `BOOLEAN`.

    CREATE OR REPLACE PROCEDURE boolproc(x boolean)
    AS
    BEGIN
    [...]
    END;

    CREATE OR REPLACE PROCEDURE boolwrap(x int)
    AS
    BEGIN
    IF (x=1) THEN
      boolproc(TRUE);
    ELSE
      boolproc(FALSE);
    END IF;
    END;

    // Create the database connection from a DataSource
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:oci:@<...hoststring...>");
    ods.setUser("scott");
    ods.setPassword("tiger");
    Connection conn = ods.getConnection();
    CallableStatement cs = conn.prepareCall ("begin boolwrap(?); end;");
    cs.setInt(1, 1);
    cs.execute ();

### Opening More Than 16 OCI Connections for a Process

You might find that you are not able to open more than approximately 16 JDBC-OCI connections for a process at any given time. The most likely reasons for this would be either that the number of processes on the server exceeded the limit specified in the initialization file, or that the per-process file descriptors limit was exceeded. It is important to note that one JDBC-OCI connection can use more than one file descriptor (it might use anywhere between 3 and 4 file descriptors).

If the server allows more than 16 processes, then the problem could be with the per-process file descriptor limit. The possible solution would be to increase this limit.

### Using statement.cancel

The JDBC standard method `Statement.cancel` attempts to cleanly stop the execution of a SQL statement by sending a message to the database. In response, the database stops execution and replies with an error message. The Java thread that invoked `Statement.execute` waits on the server, and continues execution only when it receives the error reply message invoked by the other thread's call to `Statement.cancel`.

As a result, `Statement.cancel` relies on the correct functioning of the network and the database. If either the network connection is broken or the database server is hung, the client does not receive the error reply to the cancel message. Frequently, when the server process dies, JDBC receives an `IOException` that frees the thread that invoked `Statement.execute`. In some circumstances, the server is hung, but JDBC does not receive an `IOException`. `Statement.cancel` does not free the thread that initiated the `Statement.execute`.

When JDBC does not receive an `IOException`, Oracle Net may eventually time out and close the connection. This causes an `IOException` and frees the thread. This process can take many minutes. For information on how to control this time-out, see the description of the `readTimeout` property for `OracleDatasource.setConnectionProperties`. You can also tune this time-out with certain Oracle Net settings. See the Oracle Database Net Services Administrator's Guide for more information.

The JDBC standard method `Statement.setQueryTimeout` relies on `Statement.cancel`. If execution continues longer than the specified time-out interval, then the monitor thread calls `Statement.cancel`. This is subject to all the same limitations described previously. As a result, there are cases when the time-out does not free the thread that invoked `Statement.execute`.

The length of time between execution and cancellation is not precise. This interval is no less than the specified time-out interval but can be several seconds longer. If the application has active threads running at high priority, then the interval can be arbitrarily longer. The monitor thread runs at high priority, but other high priority threads may keep it from running indefinitely. Note that the monitor thread is started only if there are statements executed with non zero time-out. There is only one monitor thread that monitors all Oracle JDBC statement execution.

`Statement.cancel` and `Statement.setQueryTimeout` are not supported in the server-side internal driver. The server-side internal driver runs in the single-threaded server process; the Oracle JVM implements Java threads within this single-threaded process. If the server-side internal driver is executing a SQL statement, then no Java thread can call `Statement.cancel`. This also applies to the Oracle JDBC monitor thread.

### Using JDBC with Firewalls

Firewall timeout for idle-connections may sever a connection. This can cause JDBC applications to hang while waiting for a connection. You can perform one or more of the following actions to avoid connections from being severed due to firewall timeout:

- If you are using connection caching or connection pooling, then always set the inactivity timeout value on the connection cache to be shorter than the firewall idle timeout value.

- Pass `oracle.net.READ_TIMEOUT` as connection property to enable read timeout on socket. The timeout value is in milliseconds.

- For both JDBC OCI and JDBC Thin drivers, use net descriptor to connect to the database and specify the `ENABLE=BROKEN` parameter in the `DESCRIPTION` clause in the connect descriptor. Also, set a lower value for `tcp_keepalive_interval`.

- Enable Oracle Net DCD by setting `SQLNET.EXPIRE_TIME=1` in the `sqlnet.ora` file on the server-side.

## Basic Debugging Procedures

This section describes strategies for debugging a JDBC program:

- Oracle Net Tracing to Trap Network Events

- Third Party Debugging Tools

For information about processing SQL exceptions, including printing stack traces to aid in debugging, see "Processing SQL Exceptions".

### Oracle Net Tracing to Trap Network Events

You can enable client and server Oracle-Net trace to trap the packets sent over Oracle Net. You can use client-side tracing only for the JDBC OCI driver; it is not supported for the JDBC Thin driver. You can find more information on tracing and reading trace files in the Oracle Net Services Administrator's Guide.

The trace facility produces a detailed sequence of statements that describe network events as they execute. "Tracing" an operation lets you obtain more information on the internal operations of the event. This information is output to a readable file that identifies the events that led to the error. Several Oracle Net parameters in the `SQLNET.ORA` file control the gathering of trace information. After setting the parameters in `SQLNET.ORA`, you must make a new connection for tracing to be performed.

The higher the trace level, the more detail is captured in the trace file. Because the trace file can be hard to understand, start with a trace level of 4 when enabling tracing. The first part of the trace file contains connection handshake information, so look beyond this for the SQL statements and error messages related to your JDBC program.

Note: The trace facility uses a large amount of disk space and might have significant impact upon system performance. Therefore, enable tracing only when necessary.

#### Client-Side Tracing

Set the following parameters in the `SQLNET.ORA` file on the client system.

##### TRACE_LEVEL_CLIENT

Purpose:

Turns tracing on/off to a certain specified level.

Default Value:

0 or OFF

Available Values:

- 0 or OFF - No trace output

- 4 or USER - User trace information

- 10 or ADMIN - Administration trace information

- 16 or SUPPORT - WorldWide Customer Support trace information

Example:

TRACE_LEVEL_CLIENT=10

##### TRACE_DIRECTORY_CLIENT

Purpose:

Specifies the destination directory of the trace file.

Default Value:

`ORACLE_HOME``/network/trace`

Example:

UNIX: `TRACE_DIRECTORY_CLIENT=/oracle/traces`

Windows: `TRACE_DIRECTORY_CLIENT=C:\ORACLE\TRACES`

##### TRACE_FILE_CLIENT

Purpose:

Specifies the name of the client trace file.

Default Value:

`SQLNET.TRC`

Example:

`TRACE_FILE_CLIENT=cli_Connection1.trc`

Note: Ensure that the name you choose for the `TRACE_FILE_CLIENT` file is different from the name you choose for the `TRACE_FILE_SERVER` file.

##### TRACE_UNIQUE_CLIENT

Purpose:

Gives each client-side trace a unique name to prevent each trace file from being overwritten with the next occurrence of a client trace. The PID is attached to the end of the file name.

Default Value:

`OFF`

Example:

`TRACE_UNIQUE_CLIENT = ON`

#### Server-Side Tracing

Set the following parameters in the `SQLNET.ORA` file on the server system. Each connection will generate a separate file with a unique file name.

##### TRACE_LEVEL_SERVER

Purpose:

Turns tracing on/off to a certain specified level.

Default Value:

0 or `OFF`

Available Values:

- 0 or `OFF` - No trace output

- 4 or `USER` - User trace information

- 10 or `ADMIN` - Administration trace information

- 16 or `SUPPORT` - WorldWide Customer Support trace information

Example:

`TRACE_LEVEL_SERVER=10`

##### TRACE_DIRECTORY_SERVER

Purpose:

Specifies the destination directory of the trace file.

Default Value:

`ORACLE_HOME``/network/trace`

Example:

`TRACE_DIRECTORY_SERVER=/oracle/traces`

##### TRACE_FILE_SERVER

Purpose:

Specifies the name of the server trace file.

Default Value:

`SERVER.TRC`

Example:

`TRACE_FILE_SERVER= svr_Connection1.trc`

Note: Ensure that the name you choose for the `TRACE_FILE_SERVER` file is different from the name you choose for the `TRACE_FILE_CLIENT` file.

### Third Party Debugging Tools

You can use tools such as JDBCSpy and JDBCTest from Intersolv to troubleshoot at the JDBC API level. These tools are similar to ODBCSpy and ODBCTest.